/****************tarifs**************************/

drop type IF EXISTS  ttarif cascade;
create type ttarif as (
				valid time_part, --time during tarif in action
				bytes_cost money, -- bytes price (may be 0) - ()
				time_cost money --time price(may be 0)  -( 1 sec)
				);

--some dumbs 	for ordering tarifs	
create or replace function not_null_tarif(ttarif) returns ttarif as
$$
declare res ttarif;
begin
       res=  ($1.valid,$1.bytes_cost,$1.time_cost)::ttarif;
       
        if (res.bytes_cost is null) then
		res.bytes_cost=0;
	end if;
	if (res.time_cost is null) then
		res.time_cost=0;
	end if;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_big(ttarif,ttarif) returns boolean as
$$
declare t1 ttarif;
	    t2 ttarif;
begin
	t1=not_null_tarif($1);
	t2=not_null_tarif($2);
	if (t1.bytes_cost+t1.time_cost)>(t2.bytes_cost+t2.time_cost)  then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;
--select op_big(((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'3'::money,null::money)::ttarif);


create or replace function op_small(ttarif,ttarif) returns boolean as
$$
declare t1 ttarif;
	    t2 ttarif;
begin
	t1=not_null_tarif($1);
	t2=not_null_tarif($2);
	if (t1.bytes_cost+t1.time_cost)<(t2.bytes_cost+t2.time_cost)  then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;
--select op_small(((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif);

create or replace function op_equals(ttarif,ttarif) returns boolean as
$$
declare t1 ttarif;
	    t2 ttarif;
begin
	t1=not_null_tarif($1);
	t2=not_null_tarif($2);
	if (t1.bytes_cost+t1.time_cost)=(t2.bytes_cost+t2.time_cost)  then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;
--select op_equals(((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif);

create or replace function op_not_equals(ttarif,ttarif) returns boolean as
$$
begin
	if not op_equals($1,$2)  then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;
--select op_not_equals(((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif);
create or replace function op_big_equals(ttarif,ttarif) returns boolean as
$$
declare t1 ttarif;
	    t2 ttarif;
begin
       t1=not_null_tarif($1);
       t2=not_null_tarif($2);
	if  not ((t1.bytes_cost+t1.time_cost)<(t2.bytes_cost+t2.time_cost)) then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;
--select op_big_equals(((now()::time_point,now()::time_point)::time_part,'121'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif);
create or replace function op_small_equals(ttarif,ttarif) returns boolean as
$$
declare t1 ttarif;
	    t2 ttarif;
begin
       t1=not_null_tarif($1);
       t2=not_null_tarif($2);
	if  not ((t1.bytes_cost+t1.time_cost)>(t2.bytes_cost+t2.time_cost)) then
	  return true;
	else
	  return false;
	 end if;
end;
$$ immutable language plpgsql;

create or replace function op_max(ttarif,ttarif)  returns ttarif as
$$
begin
	if op_big($1,$2) then
		return $1;
	else 
		return $2;
	end if;
end
$$ immutable language plpgsql;

create or replace function op_min(ttarif,ttarif)  returns ttarif as
$$
begin
	if op_small($1,$2) then
		return $1;
	else 
		return $2;
	end if;
end
$$ immutable language plpgsql;

--select op_small_equals(((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif,((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif);

create operator = (procedure =op_equals,leftarg=ttarif,rightarg=ttarif,commutator= =);
--select ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif= ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif;
create operator <> (procedure =op_not_equals,leftarg=ttarif,rightarg=ttarif,commutator= <>);
--select ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif<> ((now()::time_point,now()::time_point)::time_part,'3'::money,null::money)::ttarif;
create operator < (procedure =op_small,leftarg=ttarif,rightarg=ttarif);
--select ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif< ((now()::time_point,now()::time_point)::time_part,'23'::money,null::money)::ttarif;
create operator > (procedure =op_big,leftarg=ttarif,rightarg=ttarif);
--select ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif> ((now()::time_point,now()::time_point)::time_part,'3'::money,null::money)::ttarif;
create operator >=(procedure =op_big_equals,leftarg=ttarif,rightarg=ttarif);
--select ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif>= ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif;
create operator <=(procedure =op_small_equals,leftarg=ttarif,rightarg=ttarif);
--select ((now()::time_point,now()::time_point)::time_part,'1'::money,null::money)::ttarif<= ((now()::time_point,now()::time_point)::time_part,'12'::money,null::money)::ttarif;

create or replace function op_supp(ttarif,ttarif) returns integer as
$$
	declare res integer;
begin
	if op_small($1,$2) then
	  res=-1;
	end if;
	if op_equals($1,$2) then
	  res=0;
	 end if;
	if op_big($1,$2) then
	  res=1;
	end if;
	return res;
end;
$$ immutable language plpgsql;

drop aggregate if exists max(ttarif);
create aggregate max(ttarif) 
				(stype=ttarif,
	  			 sfunc=op_max);     

drop aggregate if exists min (ttarif);
create aggregate min(ttarif)
				(stype=ttarif,
	  			 sfunc=op_min);
				 
--- warn, on system account -----------------
\c - pgsql

drop operator class IF EXISTS ttarif_ops using btree cascade;

create operator class ttarif_ops
	default for type ttarif using btree as
		operator 1 < ,
		operator 2 <= ,
		operator 3 = ,
		operator 4 >= ,
		operator 5 >,
		function 1 op_supp(ttarif,ttarif) ;
\c - radius		
-------------------------------------

create or replace function time_cost(ttarif,time_part) returns money as
$$
declare res money;
	    isect interval;
begin
     if ($1.time_cost is null) or ($1.time_cost='0'::money) then
     	return '0'::money;
     end if;
     isect=length(op_intersect($1.valid,$2));
     if not (isect is null) and not (isect='0'::interval) then
     	res=extract(epoch from isect)*$1.time_cost;
     else
       res='0'::money;
     end if;
     return res;
end;
$$ immutable language plpgsql;

create or replace function bytes_cost(ttarif,time_part,bigint) returns money as
$$
declare res money;
	    isect interval;
 begin
       if ($1.bytes_cost is null) or ($1.time_cost='0'::money) then
     		return '0'::money;
        end if;
	isect=length(op_intersect($1.valid,$2));
	if not (isect is null) and not (isect='0'::interval) then
	         res=($3/(extract(epoch from isect)))*($1.bytes_cost);
	else
		res='0'::money;
	end if;
	return res;
	
end;
$$ immutable language plpgsql;

create or replace view vsessioncost as
select acctsessionid as SessionID,username,
	  acctstarttime::time_point as SessionStartTime,
	  acctstoptime::time_point as SessionStopTime,
	  acctstoptime::time_point-acctstarttime::time_point as SessionTotalTime,
	  acctoutputoctets as SessionOutputBytes,
	  acctinputoctets as SessionInputBytes,
	  acctoutputoctets+acctinputoctets as SessionTotalBytes,
          time_cost(tarif,(acctstarttime::time_point,acctstoptime::time_point)::time_part) as SessionTimeCost,
	  bytes_cost(tarif,(acctstarttime::time_point,acctstoptime::time_point)::time_part,acctoutputoctets+acctinputoctets) as SessionBytesCost,
from statcollector,tarifs order by 1;	  


create or replace view vtotalcost as 
select username,sum(SessionTotalTime) as SumTotalTime,Sum(SessionTotalBytes) as SumTotalBytes,Sum(SessionTimeCost) as SumTimeCost,
	 sum(SessionBytesCost) as SumBytesCost from vsessioncost group by username;

drop table if exists mv_sessioncost cascade; 	 
create table mv_sessioncost (d time_point,
                                              SessionID varchar(32),
                                              username VARCHAR(253),
	  				    SessionStartTime time_point,
					    SessionStopTime time_point,
					    SessionTotalTime interval,
					    SessionOutputBytes bigint,
					    SessionInputBytes bigint,
	                                     SessionTotalBytes bigint,
					   SessionTimeCost money,
	                                     SessionBytesCost money);	

					     
drop table if exists mv_totalcost cascade;
create table mv_totalcost(d time_point,
                                         username VARCHAR(253), 
                                         SumTotalTime interval,
				       SumTotalBytes bigint,
				       SumTimeCost  money,
				       SumBytesCost money);
alter table mv_totalcost add constraint pk_mv_totalcost primary key(d,username); 				       

drop table if exists mv_time cascade; 
create table mv_time(d time_point);

alter table mv_time add constraint pk_mv_time primary key (d);

alter table mv_totalcost add constraint fk_time_totalcost foreign key (d) references mv_time;

alter table mv_sessioncost add constraint fk_time_totalcost foreign key (d) references mv_time;


create or replace function update_mv_sessioncost(time_point) returns void as
'
insert into mv_sessioncost select $1,a.* from vsessioncost a;
' language sql;

create or replace function update_mv_totalcost(time_point) returns void as
'
insert into mv_totalcost select $1,a.* from vtotalcost a;
' language sql;

create or replace function update_mv() returns time_point as
$$
declare 
     d time_point;
begin
        d=now_time_point();
	insert into mv_time values (d);
    perform    update_mv_sessioncost(d);
    perform    update_mv_totalcost(d);
      return d;
      Exception
      


end;
$$ language plpgsql;


	    
	    
	   
